SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.tempValcom AS
BEGIN
SELECT      MAX(md.[ASSET ID]) 'SSBID'
     , MAX(ca.ExtId) 'ARTID'
           , md.[ASSET DESCRIPTION]
           , md.[ISSUE DESCRIPTION]
		   , md.[UNDERLYING FUND #]
		    , MAX(ca.PortfolioId)	'PF'
             --    , md.[UNDERLYING FUND #]
           , SUM(md.UNITS)               par
		      , MAX(ca.EndAdjAmt) 'ARTYPAR'
			  , SUM(md.[BASE TOTAL COST]) 'AMORT'
           --, SUM(md.[BASE MARKET VALUE]) mv
           --, SUM(md.[BASE TOTAL COST])   cost
           , MAX(ca.InstrName) 'IntsN'
           , MAX(ca.InstrClass) 'Class'
       
        
		  
 FROM        dbo.MVADate md
             INNER JOIN dbo.VALCOM_SSB_CUSIP_Map map
             ON          map.SSB_ID = md.[ASSET ID]
			 INNER JOIN  dbo.mapSSB_PortID pM
			 ON pm.SSB_PortfolioID = md.[UNDERLYING FUND #]

             CROSS APPLY
                         ( SELECT    i.InstrName
                                  , id.InstrClass
                                  , i.ExtId
								  , a.PortfolioId
									, SUM( a.EndAdjAmt) EndAdjAmt
                         FROM       FSFundModel_VALCOMM.Reporting.Asset a
                                    INNER JOIN FSFundModel_VALCOMM.Reporting.Instrument i
                                    ON         a.ExportId     = i.ExportId
                                    AND        a.InstrumentId = i.InstrumentId
                                    INNER JOIN FSFundModel_VALCOMM.Reporting.InstrumentDefinition id
                                    ON         a.ExportId  = id.ExportId
                                    AND        i.InstDefId = id.InstDefId
                         WHERE      a.ExportId             =
                                    ( SELECT MAX(e.ExportId)
                                    FROM    FSFundModel_VALCOMM.Reporting.Export e
                                    WHERE   e.FundId = 'FSIC' )
                         AND        a.IsActiveEndOfDay = 1
                        
						 GROUP BY i.InstrName, id.InstrClass, i.ExtId, a.PortfolioId
						 
						 ) ca

	WHERE  map.FS_ID          = ca.ExtId 
						 AND	pm.ART_PortfolioID = ca.PortfolioId
					
 GROUP BY
             md.[UNDERLYING FUND #],
             md.[ASSET DESCRIPTION]
           , md.[ISSUE DESCRIPTION]
             -- , md.[UNDERLYING FUND #]
             --, md.[ASSET ID]
 ORDER BY    md.[ASSET DESCRIPTION]
           , md.[ISSUE DESCRIPTION]
END

GO
EXEC sp_addextendedproperty N'VirtualFolder', N'Dbo/QuarterlyReporting/Valcom', 'SCHEMA', N'dbo', 'PROCEDURE', N'tempValcom', NULL, NULL
GO
